import pandas as pd
import numpy as np
import lightgbm as lgb
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score
import matplotlib.pyplot as plt
import joblib  # 用于导出模型
import numpy as np
from lightgbm import log_evaluation,early_stopping

predictDate = '2025-01-01'
history_files = 'sales-LO2502025.xlsx'
# history_files = 'lilylikes-all-LO2312019AXNHS.csv'
# history_files = 'lilylikes-all-LO2312019AXNHS.csv'

def rmse(y_true, y_pred):
    """
    计算 RMSE
    :param y_true: 真实值，数组或列表
    :param y_pred: 预测值，数组或列表
    :return: RMSE 值
    """
    return np.sqrt(np.mean((np.array(y_true) - np.array(y_pred)) ** 2))

def fill_missing_dates(df):
    # 确保 date_id 列是 datetime 类型
    df['date_id'] = pd.to_datetime(df['date_id'], format='%Y-%m-%d')

    # 生成完整日期范围
    min_date = df['date_id'].min()
    max_date = df['date_id'].max()
    all_dates = pd.date_range(min_date, max_date, freq='D')
    # 创建完整日期和 spu_code 的组合
    full_df = pd.DataFrame(all_dates, columns=['date_id'])
    # 合并原始数据
    full_df = full_df.merge(df, on=['date_id'], how='left')

    # 填充缺失的 daily_sale_qty 为 0
    full_df['daily_sale_qty'] = full_df['daily_sale_qty'].fillna(0)
    full_df['spu_code'] = full_df['spu_code'].fillna(df[df['spu_code'].notna()].iloc[0]['spu_code'])
    full_df['new_date'] = full_df['new_date'].fillna(df[df['new_date'].notna()].iloc[0]['new_date'])
    # full_df['middle_class_name'] = full_df['middle_class_name'].fillna(df[df['middle_class_name'].notna()].iloc[0]['middle_class_name'])
    # 将 middle_class_name 转换为分类类型
    # full_df['middle_class_name'] = full_df['middle_class_name'].astype(str)
    # full_df['middle_class_name'] = full_df['middle_class_name'].astype('category').cat.codes
    return full_df

# 生成示例数据
df = pd.read_excel(history_files)
# df = pd.read_csv('lilylikes-all-LO23080610WHS.csv', low_memory=False, encoding='GBK')
df['date_id'] = pd.to_datetime(df['date_id'], format='%Y-%m-%d')
df = fill_missing_dates(df)
df['new_date'] = pd.to_datetime(df['new_date'], format='%Y-%m-%d')
df['product_age'] = (df['date_id'] - df['new_date']).dt.days

df.set_index('date_id', inplace=True)
# 添加时间特征
# df['day_of_year'] = df.index.dayofyear
# df['day_of_week'] = df.index.dayofweek
# df['day_of_month'] = df.index.day
# df['month'] = df.index.month
# df['is_weekend'] = df['day_of_week'].isin([5, 6]).astype(int)
for lag in [1,7,14]:
    df[f'lag_{lag}'] = df['daily_sale_qty'].shift(lag)
for window in [7, 14, 30]:
    df[f'rolling_mean_{window}'] = df.groupby('date_id')['daily_sale_qty'].transform(
        lambda x: x.rolling(window, min_periods=1).mean())
# # # 滑动窗口统计
for window in [3,7, 14, 30]:
    df[f'rolling_sum_{window}'] = df['daily_sale_qty'].transform(
        lambda x: x.rolling(window, min_periods=1).sum()
    )
#     df[f'rolling_std_{window}'] = df.groupby('date_id')['daily_sale_qty'].transform(
#         lambda x: x.rolling(window, min_periods=1).std()
#     )

# 特征和目标变量
X = df[[
    # 'day_of_year','day_of_week', 'day_of_month', 'month','is_weekend',
    # 'middle_class_name',
    'product_age',
    'lag_1', 'lag_7', 'lag_14',
    'rolling_sum_3',
    'rolling_sum_7',
    'rolling_sum_14',
    'rolling_sum_30'
    # ,
    # 'rolling_std_7',
    # 'rolling_std_14',
    # 'rolling_std_30',
    #  'rolling_mean_7','rolling_mean_14'
    # ,'rolling_mean_30'
        ]]
y = df['daily_sale_qty']

# 划分训练集和测试集
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 创建 LightGBM 数据集
# categorical_features = ['middle_class_name']
# train_data = lgb.Dataset(X_train, label=y_train, categorical_feature=categorical_features)
train_data = lgb.Dataset(X_train, label=y_train)
test_data = lgb.Dataset(X_test, label=y_test, reference=train_data)

# 定义 LightGBM 参数
params = {
    'objective': 'regression',  # 回归任务
    'metric': 'rmse',           # 评估指标为均方根误差
    'boosting_type': 'gbdt',    # 使用 GBDT 算法
    'num_leaves': 31,           # 叶子节点数
    'learning_rate': 0.05,      # 学习率
    'feature_fraction': 0.9,    # 特征采样比例
    'bagging_fraction': 0.8,    # 数据采样比例
    'bagging_freq': 5,          # 每 5 次迭代进行一次 bagging
    'verbose': -1               # 不输出日志
}

# 训练模型
model = lgb.train(
    params,
    train_data,
    num_boost_round=1000,        # 迭代次数
    valid_sets=[test_data],
    callbacks=[log_evaluation(period=5),
               early_stopping(stopping_rounds=10)
               ]  # 每隔5轮打印一次
    ,      # 验证集
    # early_stopping_rounds=10    # 早停轮数
)

# 导出模型到文件
model_filename = '../lightgbm_model.pkl'
joblib.dump(model, model_filename)
print(f"模型已导出到文件：{model_filename}")


# 预测测试集
y_pred = model.predict(X_test, num_iteration=model.best_iteration)

# 评估模型
mse = mean_squared_error(y_test, y_pred)
# mae = mean_absolute_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print(f"R2 分数：{r2}")
print(f"均方误差（MSE）：{mse}")
print("均方根误差 RMSE:", rmse(y_test, y_pred))

# 生成未来7天的日期

# future_dates = pd.date_range(start='2024-12-27', periods=7, freq='D')
future_dates = pd.date_range(start=predictDate, periods=7, freq='D')
# future_dates = pd.date_range(start='2025-03-18', periods=7, freq='D')
# 创建未来7天的特征

print(len(future_dates))
print(df['new_date'].iloc[-1])

print(11111111111111)
print(len((future_dates - df['new_date'].iloc[-1]).days))

future_df = pd.DataFrame({
    'date_id': future_dates,
    # 'day_of_year': future_dates.dayofyear,
    # 'day_of_week': future_dates.dayofweek,
    # 'day_of_month': future_dates.day,
    # 'month': future_dates.month,
    # 'is_weekend': future_dates.isin([5, 6]).astype(int),
    # 'middle_class_name':df['middle_class_name'].iloc[-1],
    # 'product_age': [370, 371, 372, 373, 374, 375, 376], # 1.1日值预测 LO2312019AXNHS
    'product_age':(future_dates - df['new_date'].iloc[-1]).days,
    # 'lag_1': [3, 4, 2, 1, 4, 2, 4],  # 手工输入值
    # 'lag_7': [2, 4, 4, 4, 4, 1, 3],
    # 'lag_14': [2, 9, 3, 4, 6, 5, 6],
    # 'lag_1': [0, 0, 1, 0, 0, 2, 0],  # 手工输入值
    # 'lag_7': [0, 0, 0, 1, 0, 1, 0],
    # 'lag_14': [0, 0, 0, 0, 0, 0, 0],
    'lag_1': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['lag_1'],  # 用最后一天数据作为滞后值*future_dates.size,
    'lag_7': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['lag_7'],
    'lag_14': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['lag_14'],
    # 'rolling_std_3': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_std_3'],
    # 'rolling_std_7':df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_std_7'],
    # 'rolling_std_14':df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_std_14'],
    'rolling_sum_3': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_sum_3'],
    'rolling_sum_7':df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_sum_7'],
    'rolling_sum_14':df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_sum_14'],
    'rolling_sum_30':df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_sum_30'],
    # 'rolling_mean_7': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_mean_7'],  # 用最后一天数据作为滞后值*future_dates.size,
    # 'rolling_mean_14': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_mean_14'],
    # 'rolling_mean_30': df.loc[df.index.isin(future_dates - pd.DateOffset(days=len(future_dates)))]['rolling_mean_30'],
    # 'rolling_mean_7': [12, 12, 10, 10, 10, 10, 10],
    # 'rolling_mean_14': [12, 12, 10, 10, 10, 10, 10],
    # 'rolling_mean_30': [12, 12, 10, 10, 10, 10, 10],

})

# 预测特征值
temp = future_df[[
    # 'day_of_year','day_of_week', 'day_of_month', 'month','is_weekend',
    # 'middle_class_name',
    'product_age',
    'lag_1', 'lag_7', 'lag_14',
    # 'rolling_std_3',
    # 'rolling_std_7',
    #  'rolling_std_14',
    'rolling_sum_3',
    'rolling_sum_7',
     'rolling_sum_14',
     'rolling_sum_30',
    # 'last_7day_qty'
    # 'rolling_mean_7', 'rolling_mean_14'
    # , 'rolling_mean_30'
]]
# 预测未来7天的销量
future_sales = model.predict(
    temp,
    num_iteration=model.best_iteration,
    # #检查值是否相等
    # predict_disable_shape_check=True
 )

# 将预测结果添加到 DataFrame
future_df['predicted_sales'] = future_sales
future_df['predicted_sales'] = future_df['predicted_sales'].apply(lambda x: max(x, 0))
# 查看预测结果
print(future_df[['date_id', 'predicted_sales']])
# 可视化历史数据和预测结果
plt.figure(figsize=(12, 6))
plt.plot(df.loc[df.index.isin(future_dates)].index,df.loc[df.index.isin(future_dates)]['daily_sale_qty'], label='Historical Sales')
plt.plot(future_df['date_id'], future_df['predicted_sales'], label='Predicted Sales', linestyle='--', marker='o')
plt.xlabel('date_id')
plt.ylabel('daily_sale_qty')
plt.title('Historical and Predicted Sales (LightGBM)')
plt.legend()
plt.show()
future_7day_qty = future_df['predicted_sales'].sum()
real_7day_qty = df.loc[df.index.isin(future_dates)]['daily_sale_qty'].sum()
print(f"预测值={future_7day_qty:.4f}\n"
      f"真实值={real_7day_qty}\n"
      f"百分比相差={(future_7day_qty/(0.001 if real_7day_qty == 0 else real_7day_qty)  -1):.2%}")
